package com.library.dao;

import com.library.util.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;

public class PayrecDao {
    private final QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());

    // 插入付款记录，返回新生成的serNum
    public int insertPayrec(String sno, double payAmount) {
        String sql = "INSERT INTO payrec(sno, payAmount, payDate) VALUES (?, ?, CURDATE())";
        try {
            queryRunner.update(sql, sno, payAmount);
            // 获取自增主键
            String idSql = "SELECT LAST_INSERT_ID()";
            Object serNumObj = queryRunner.query(idSql, new ScalarHandler<>());
            int serNum = 0;
            if (serNumObj instanceof Integer) {
                serNum = (Integer) serNumObj;
            } else if (serNumObj instanceof Long) {
                serNum = ((Long) serNumObj).intValue();
            } else if (serNumObj instanceof java.math.BigInteger) {
                serNum = ((java.math.BigInteger) serNumObj).intValue();
            }
            return serNum;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 获取所有付款记录
     * @return
     */
    public java.util.List<com.library.entity.Payrec> listAll() {
        String sql = "select * from payrec order by payDate desc";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Payrec.class));
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 分页获取付款记录
     * @param pageNum 页码（从1开始）
     * @param pageSize 每页显示数量
     * @return 当前页的付款记录列表
     */
    public java.util.List<com.library.entity.Payrec> listByPage(int pageNum, int pageSize) {
        int startIndex = (pageNum - 1) * pageSize;
        String sql = "select * from payrec order by payDate desc limit ?, ?";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Payrec.class), startIndex, pageSize);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    /**
     * 获取付款记录总数
     * @return 付款记录总数
     */
    public int getTotalCount() {
        String sql = "select count(*) from payrec";
        try {
            Long count = queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>());
            return count.intValue();
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据学号搜索付款记录并分页
     * @param keyword 学号关键词
     * @param pageNum 页码
     * @param pageSize 每页显示数量
     * @return 符合条件的付款记录列表
     */
    public java.util.List<com.library.entity.Payrec> searchPayrecsByPage(String keyword, int pageNum, int pageSize) {
        int startIndex = (pageNum - 1) * pageSize;
        String sql = "select * from payrec where sno like ? order by payDate desc limit ?, ?";
        try {
            String searchPattern = "%" + keyword + "%";
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Payrec.class),
                    searchPattern, startIndex, pageSize);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    /**
     * 获取搜索结果的总数
     * @param keyword 学号关键词
     * @return 符合条件的付款记录总数
     */
    public int getSearchTotalCount(String keyword) {
        String sql = "select count(*) from payrec where sno like ?";
        try {
            String searchPattern = "%" + keyword + "%";
            Long count = queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), searchPattern);
            return count.intValue();
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据学号和日期范围综合查询并分页
     * @param keyword 学号关键词（可为null）
     * @param startDate 开始日期（可为null）
     * @param endDate 结束日期（可为null）
     * @param pageNum 页码
     * @param pageSize 每页显示数量
     * @return 符合条件的付款记录列表
     */
    public java.util.List<com.library.entity.Payrec> searchPayrecsWithFiltersAndPage(String keyword, java.util.Date startDate, java.util.Date endDate, int pageNum, int pageSize) {
        int startIndex = (pageNum - 1) * pageSize;
        StringBuilder sqlBuilder = new StringBuilder("select * from payrec where 1=1");
        java.util.List<Object> params = new java.util.ArrayList<>();
        if (keyword != null && !keyword.trim().isEmpty()) {
            sqlBuilder.append(" and sno like ?");
            params.add("%" + keyword.trim() + "%");
        }
        if (startDate != null) {
            sqlBuilder.append(" and DATE(payDate) >= DATE(?)");
            params.add(startDate);
        }
        if (endDate != null) {
            // 增加一天
            Calendar cal = Calendar.getInstance();
            cal.setTime(endDate);
            cal.add(Calendar.DATE, 1);
            Date nextDay = cal.getTime();
            sqlBuilder.append(" and payDate < ?");
            params.add(nextDay);
        }
        sqlBuilder.append(" order by payDate desc limit ?, ?");
        params.add(startIndex);
        params.add(pageSize);
        try {
            return queryRunner.query(sqlBuilder.toString(), new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Payrec.class), params.toArray());
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    /**
     * 根据学号和日期范围综合查询总数
     * @param keyword 学号关键词（可为null）
     * @param startDate 开始日期（可为null）
     * @param endDate 结束日期（可为null）
     * @return 符合条件的付款记录总数
     */
    public int getCountWithFilters(String keyword, java.util.Date startDate, java.util.Date endDate) {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from payrec where 1=1");
        java.util.List<Object> params = new java.util.ArrayList<>();
        if (keyword != null && !keyword.trim().isEmpty()) {
            sqlBuilder.append(" and sno like ?");
            params.add("%" + keyword.trim() + "%");
        }
        if (startDate != null) {
            sqlBuilder.append(" and DATE(payDate) >= DATE(?)");
            params.add(startDate);
        }
        if (endDate != null) {
            sqlBuilder.append(" and DATE(payDate) <= DATE(?)");
            params.add(endDate);
        }
        try {
            Long count = queryRunner.query(sqlBuilder.toString(), new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), params.toArray());
            return count.intValue();
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
} 